
package serviceWeb.modelePostgreSQL;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLXML;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

/**
 * Class EchangeBDXML qui gère le dialogue avec une base PostgreSQL et qui récupère
 * les données sous forme de XML.
 */
public class EchangeBDXML implements serviceWeb.EchangeBDXML
{
    private SQLXML xml;

    public SQLXML getXML()
    {
        return xml;
    }

    //Cela suppose que la base 'postgres' existe (elle est généralement incluse par défaut)
    public void getBases() throws SQLException
    {
        this.xml = null;

        String requete = "select query_to_xml('select datname from pg_database where datname != ''template1'' and datname !=''template0''', true, false, '')";
        Connection c = Database.getConnection("postgres");
        PreparedStatement ps = c.prepareStatement(requete);
        ResultSet rs = ps.executeQuery();

        if(rs.next())
            this.xml = rs.getSQLXML(1);

    }

    public void getToutesLesTables(String nomBD) throws SQLException
    {
        this.xml = null;

        String requete = "select query_to_xml('SELECT tablename FROM pg_tables WHERE tablename !~ ''^pg_'' and tablename !~ ''^sql_'' order by tablename', true, false, '')";
        Connection c = Database.getConnection(nomBD);
        PreparedStatement ps = c.prepareStatement(requete);
        ResultSet rs = ps.executeQuery();

        if(rs.next())
            this.xml = rs.getSQLXML(1);

        c.close();
    }

    public void getTableSchema(String nomBD, String nomTable) throws SQLException
    {
        this.xml = null;

        String requete = "select table_to_xmlschema(?, true, false, '');";
        Connection c = Database.getConnection(nomBD);
        PreparedStatement ps = c.prepareStatement(requete);
        ps.setString(1, nomTable);
        ResultSet rs = ps.executeQuery();

        if(rs.next())
            this.xml = rs.getSQLXML(1);

        c.close();
    }

    public void getTousLesRecords(String nomBD, String nomTable) throws SQLException
    {
        this.xml = null;

        String requete = "select table_to_xml(?, true, false, '');";
        Connection c = Database.getConnection(nomBD);
        PreparedStatement ps = c.prepareStatement(requete);
        ps.setString(1, nomTable);
        ResultSet rs = ps.executeQuery();

        if(rs.next())
            this.xml = rs.getSQLXML(1);
       
        c.close();
    }

    /**
     * On ne gère que les tables dont les identifiants n'ont qu'une seule colonne
     */
    public String getNomIdentifiant(String nomBD, String nomTable) throws SQLException
    {
        String resultat = null;

        String requete = "SELECT kcu.column_name FROM information_schema.table_constraints tc "
                + "LEFT JOIN information_schema.key_column_usage kcu ON tc.constraint_catalog = kcu.constraint_catalog "
                + "AND tc.constraint_schema = kcu.constraint_schema AND tc.constraint_name = kcu.constraint_name "
                + "WHERE lower(tc.constraint_type) in ('primary key') and lower(tc.table_name) in (?)";

        Connection c = Database.getConnection(nomBD);
        PreparedStatement ps = c.prepareStatement(requete);
        ps.setString(1, nomTable);
        ResultSet rs = ps.executeQuery();

        if(rs.next())
            resultat = rs.getString("column_name");

        c.close();

        return resultat;
    }

    public Map<String, String> getForeignKeys(String nomBD, String nomTable) throws SQLException
    {
        Map<String, String> resultat = new HashMap<String, String>();

        String requete = "SELECT distinct kcu.column_name, ccu.table_name AS references_table FROM information_schema.table_constraints tc "
                + "LEFT JOIN information_schema.key_column_usage kcu ON tc.constraint_catalog = kcu.constraint_catalog "
                + "AND tc.constraint_schema = kcu.constraint_schema AND tc.constraint_name = kcu.constraint_name "
                + "LEFT JOIN information_schema.referential_constraints rc ON tc.constraint_catalog = rc.constraint_catalog "
                + "AND tc.constraint_schema = rc.constraint_schema AND tc.constraint_name = rc.constraint_name "
                + "LEFT JOIN information_schema.constraint_column_usage ccu ON rc.unique_constraint_catalog = ccu.constraint_catalog "
                + "AND rc.unique_constraint_schema = ccu.constraint_schema AND rc.unique_constraint_name = ccu.constraint_name "
                + "WHERE lower(tc.constraint_type) in ('foreign key') and lower(tc.table_name) in (?)";

        Connection c = Database.getConnection(nomBD);
        PreparedStatement ps = c.prepareStatement(requete);
        ps.setString(1, nomTable);
        ResultSet rs = ps.executeQuery();

        while(rs.next())
        {
            String columnName = rs.getString("column_name");
            String referencesTable = rs.getString("references_table");

            resultat.put(columnName, referencesTable);
        }

        c.close();

        return resultat;
    }

    public void getRecord(String nomBD, String nomTable, String identifiant) throws SQLException
    {
        this.xml = null;

        String nomIdentifiant = getNomIdentifiant(nomBD, nomTable);

        //Obtenir le type de la clé primaire
        /*requete = "SELECT data_type FROM INFORMATION_SCHEMA.Columns where column_name = ? and table_name = ?";
        ps = c.prepareStatement(requete);
        ps.setString(1, nomIdentifiant);
        ps.setString(2, nomTable);
        rs = ps.executeQuery();

        if(rs.next())
        {
        String typeIdentifiant = rs.getString("data_type");

        //Si la clé primaire est une chaîne
        //if(typeIdentifiant.equals("character varying"))*/
        String identifiantApos = "''" + identifiant + "''";
        //pour gérer le cas où la clé primaire est une chaîne

        String requete = "select query_to_xml('SELECT * FROM " + nomTable + " WHERE " + nomIdentifiant + " = " + identifiantApos + "', true, false, '')";
        Connection c = Database.getConnection(nomBD);
        PreparedStatement ps = c.prepareStatement(requete);
        ResultSet rs = ps.executeQuery();

        if(rs.next())
            this.xml = rs.getSQLXML(1);

        c.close();
    }

    public void postRecord(String nomBD, String requete) throws SQLException
    {
        System.out.println(requete);
        Connection c = Database.getConnection(nomBD);
        PreparedStatement ps = c.prepareStatement(requete);
        ps.executeUpdate();
        c.close();
    }

    public void putRecord(String nomBD, String nomTable, String identifiant, Map<String, String> valeurs) throws SQLException
    {
        String nomIdentifiant = getNomIdentifiant(nomBD, nomTable);

        Iterator itr = valeurs.entrySet().iterator();

        String requete = "Update " + nomTable + " set ";

        Map.Entry pair = (Map.Entry) itr.next();
        requete += pair.getKey() + " = " + pair.getValue();

        while(itr.hasNext())
        {
            Map.Entry pairs = (Map.Entry) itr.next();
            requete +=  ", " + pairs.getKey() + " = " + pairs.getValue();
        }

        requete += " where " + nomIdentifiant + " = '" + identifiant + "'";

        System.out.println(requete);

        Connection c = Database.getConnection(nomBD);
        PreparedStatement ps = c.prepareStatement(requete);
        ps.executeUpdate();
        c.close();

    }

    public void deleteRecord(String nomBD, String nomTable, String identifiant) throws SQLException
    {
        String nomIdentifiant = getNomIdentifiant(nomBD, nomTable);

        String requete = "Delete FROM " + nomTable + " WHERE " + nomIdentifiant + " = '" + identifiant + "'";
        System.out.println(requete);
        
        Connection c = Database.getConnection(nomBD);
        PreparedStatement ps = c.prepareStatement(requete);
        ps.executeUpdate();
        c.close();
    }

}
